package jwdt.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Properties;

import jwdt.vo.ColumnVo;
import jwdt.vo.ExportedKeyVo;
import jwdt.vo.ImportedKeyVo;
import jwdt.vo.PrimaryKeyVo;
import jwdt.vo.TableVo;

import org.eclipse.jdt.core.IJavaProject;

public class ConnectionUtils {

	/**
	 * getConnection
	 * @param driverClass
	 * @param url
	 * @param userName
	 * @param passWord
	 * @return 
	 * @throws Exception
	 */
	public static Connection getConnection( String driverClass, 
											String url,
											String userName, 
											String passWord,
											IJavaProject javaProject) throws Exception {
		Connection con = null;

		ProjectClassLoader projectClassLoader = new ProjectClassLoader(javaProject);
		Class mysqlDriver = projectClassLoader.loadClass(driverClass);
		Driver driver = (Driver) mysqlDriver.newInstance();

		Properties prop = new Properties();
		prop.setProperty("user", userName);
		prop.setProperty("password", passWord);

		con = driver.connect(url, prop);

		return con;
	}
	
	/**
	* getTables
	* @param con
	* @param tablePattern
	* @return  
	* @throws Exception
	*/
	public static List<String> getTables(Connection con,String schemaPattern,String tablePattern) throws Exception {
		String[] names = {"TABLE"};
		String TABLE_NAME = "TABLE_NAME";
		//String TABLE_TYPE = "TABLE_TYPE";
		List<String> tables = new ArrayList<String>(0);
		
		if(schemaPattern != null && "".equals(schemaPattern.trim())){
			schemaPattern=null;
		}
		if(tablePattern != null && "".equals(tablePattern.trim())){
			tablePattern=null;
		}
		
		DatabaseMetaData metaData = con.getMetaData();
		ResultSet resultSet = metaData.getTables(null, schemaPattern, tablePattern, names);			
		while (resultSet.next()) {
			tables.add(resultSet.getString(TABLE_NAME));				
		}

		return tables;
	}

	/**
	 * getTableVos
	 * @param con
	 * @param tables
	 * @return List<TableVo>
	 */
	public static List<TableVo> getTableVos(Connection con,Collection<String> tables) throws Exception{
		List<TableVo> tableVos = new ArrayList<TableVo>();
		
		DatabaseMetaData metaData = con.getMetaData();		
		for(String str : tables){
			TableVo tableVo = new TableVo();
			tableVo.setTableName(str);
			//Columns
			List<ColumnVo> columns = new ArrayList<ColumnVo>();
			ResultSet rs = metaData.getColumns(null, null, str, null);	
			while(rs.next()){
				ColumnVo columnVo = new ColumnVo();
				columnVo.setColumnName(rs.getString(ColumnVo.COLUMN_NAME));	
				columnVo.setColumnSize(Integer.parseInt(rs.getString(ColumnVo.COLUMN_SIZE)));
				columnVo.setTypeName(rs.getString(ColumnVo.TYPE_NAME));
				columnVo.setRemarks(rs.getString(ColumnVo.REMARKS));
				columnVo.setNullable("YES".equals(rs.getString(ColumnVo.IS_NULLABLE)) ? true : false);
				columns.add(columnVo);			
			}
			rs.close();
			//PrimaryKeys
			List<PrimaryKeyVo> primaryKeys = new ArrayList<PrimaryKeyVo>();
			rs = metaData.getPrimaryKeys(null, null, str); 
			while(rs.next()){
				PrimaryKeyVo primaryKeyVo = new PrimaryKeyVo();
				String columnName = rs.getString(PrimaryKeyVo.COLUMN_NAME);
				for(ColumnVo column : columns){
					if(columnName.equals(column.getColumnName())){
						primaryKeyVo.setColumnName(columnName);
						primaryKeyVo.setColumnSize(column.getColumnSize());						
						primaryKeyVo.setNullable(column.isNullable());
						primaryKeyVo.setPrimaryKey(true);
						primaryKeyVo.setRemarks(column.getRemarks());
						primaryKeyVo.setTypeName(column.getTypeName());						
						columns.remove(column);
						primaryKeys.add(primaryKeyVo);
						break;
					}
				}
			}
			rs.close();
			//ImportedKeys
			List<ImportedKeyVo> importedKeys = new ArrayList<ImportedKeyVo>();
			rs = metaData.getImportedKeys(null, null, str); 
			while(rs.next()){
				ImportedKeyVo importedKeyVo = new ImportedKeyVo();
				String columnName = rs.getString(ImportedKeyVo.FKCOLUMN_NAME);
				for(ColumnVo column : columns){
					if(columnName.equals(column.getColumnName())){
						importedKeyVo.setColumnName(columnName);
						importedKeyVo.setColumnSize(column.getColumnSize());						
						importedKeyVo.setNullable(column.isNullable());						
						importedKeyVo.setRemarks(column.getRemarks());
						importedKeyVo.setTypeName(column.getTypeName());
						importedKeyVo.setPkcolumnName(rs.getString(ImportedKeyVo.PKCOLUMN_NAME));
						importedKeyVo.setPktableName(rs.getString(ImportedKeyVo.PKTABLE_NAME));
						columns.remove(column);
						importedKeys.add(importedKeyVo);
						break;
					}
				}				
			}			
			rs.close();
			//ExportedKeys
			List<ExportedKeyVo> exportedKeys = new ArrayList<ExportedKeyVo>();
			rs = metaData.getExportedKeys(null, null, str); 
			while(rs.next()){
				ExportedKeyVo exportedKeyVo = new ExportedKeyVo();
				String columnName = rs.getString(ExportedKeyVo.PKCOLUMN_NAME);
				for(PrimaryKeyVo primaryKeyVo : primaryKeys){
					if(columnName.equals(primaryKeyVo.getColumnName())){
						exportedKeyVo.setColumnName(columnName);
						exportedKeyVo.setColumnSize(primaryKeyVo.getColumnSize());						
						exportedKeyVo.setNullable(primaryKeyVo.isNullable());						
						exportedKeyVo.setRemarks(primaryKeyVo.getRemarks());
						exportedKeyVo.setTypeName(primaryKeyVo.getTypeName());
						exportedKeyVo.setFktableName(rs.getString(ExportedKeyVo.FKTABLE_NAME));
						exportedKeyVo.setFkcolumnName(rs.getString(ExportedKeyVo.FKCOLUMN_NAME));
						exportedKeys.add(exportedKeyVo);
						break;
					}
				}					
			}			
			rs.close();
			
			tableVo.setExportedKeys(exportedKeys);
			tableVo.setImportedKeys(importedKeys);
			tableVo.setPrimaryKeys(primaryKeys);
			tableVo.setColumns(columns);
			tableVos.add(tableVo);
		}
		return tableVos;
	}
}
